from PyQt5 import QtWidgets,QtGui
from Ui_mainwindow import  Ui_MainWindow
from PyQt5.QtCore import Qt
from PyQt5.QtGui import QIcon,QBrush,QColor
import sys
import os,logging,time,configparser,openpyxl
import pandas as pd

cfg_file = "cfg.ini"
Version = "Excel检查工具 V0.1"

class MyWindowShow(QtWidgets.QMainWindow, Ui_MainWindow):

    def __init__(self):
        super(MyWindowShow, self).__init__()
        self.setupUi(self)
        self.setWindowTitle(Version)
        self.setWindowIcon(QIcon('Flap.ico'))
        self.log_init()
        logging.info("Version:"+Version)
        self.table_para.horizontalHeader().setSectionResizeMode(QtWidgets.QHeaderView.Stretch)
        self.table_res.horizontalHeader().setSectionResizeMode(QtWidgets.QHeaderView.Stretch)
        self.btn_a.clicked.connect(self.choose_file_a)
        self.btn_b.clicked.connect(self.choose_file_b)
        self.btn_check_para.clicked.connect(self.check_para)
        self.btn_add.clicked.connect(self.add_para)
        # 开始根据参数列表比对BOM
        self.btn_start_compare.clicked.connect(self.cmp)
        # 选中单元格
        self.table_res.cellClicked.connect(self.cell_detail)
        self.cfg = configparser.ConfigParser()
        self.cfg.read(cfg_file,'utf-8')
        self.file_a=''
        self.file_b=''

    
    def get_sheet(self,file_name,key):
        logging.info("File Name:"+file_name)
        wb = openpyxl.load_workbook(file_name)
        sheet_list = []
        for each in wb.sheetnames:
            if each.find(key)>-1:
                sheet_list.append(each)
        logging.info(sheet_list)
        wb.close()
        return sheet_list

    def choose_file_a(self):
        
        self.file_a,ftype = QtWidgets.QFileDialog.getOpenFileName(self,"请选择原始Excel文件",os.getcwd(),'Excel Files (*.xlsx)')
        logging.info(self.file_a)
        if self.file_a!='':
            self.file_a = self.file_a.replace("//","\\")
            logging.info("已选择原始文件:"+str(self.file_a))
            self.statusbar.showMessage("已选择原始文件:"+str(self.file_a))
            current_dir = os.path.split(self.file_a)[0]
            os.chdir(current_dir)
            self.lineEdit_a.setText(self.file_a)
            wb = openpyxl.load_workbook(self.file_a)
            self.cBox_sa.clear()
            self.cBox_sa.addItems(wb.sheetnames)
            wb.close()
        else:
            logging.info("没有选择正确文件")
            self.statusbar.showMessage("没有选择正确文件")


    def choose_file_b(self):
        self.file_b,ftype = QtWidgets.QFileDialog.getOpenFileName(self,"请选择原始Excel文件",os.getcwd(),'Excel Files (*.xlsx)')
        if self.file_b!='':
            self.file_b = self.file_b.replace("//","\\")
            logging.info("已选择目标文件:"+str(self.file_b))
            self.statusbar.showMessage("已选择目标文件:"+str(self.file_b))
            current_dir = os.path.split(self.file_b)[0]
            os.chdir(current_dir)
            self.lineEdit_b.setText(self.file_b)
            wb = openpyxl.load_workbook(self.file_b)
            self.cBox_sb.clear()
            self.cBox_sb.addItems(wb.sheetnames)
            wb.close()
        else:
            logging.info("没有选择正确文件")
            self.statusbar.showMessage("没有选择正确文件")
    
    def check_para(self):
        logging.info("开始比对文件...")
        if self.file_a!='' and self.file_b != '':
            try:
                logging.info("Try to load file_a")
                index = self.cfg["default"]["index_key"]
                sheet_a = self.cBox_sa.currentText()
                sheet_b = self.cBox_sb.currentText()
                df = pd.read_excel(self.file_a,header=1,sheet_name=sheet_a,dtype=str)
                self.df_a = self.df_trans(df)
                self.df_a["check"] = False
                logging.info("Sheet A:"+sheet_a)
                logging.info(self.df_a.columns)
                df = pd.read_excel(self.file_b,header=1,sheet_name=sheet_b,dtype=str)
                self.df_b = self.df_trans(df)
                self.df_b["check"] = False
                logging.info("Sheet B:"+sheet_b)
                logging.info(self.df_b.columns)
                
                # 获取表格列名
                self.para_list_a = []
                for each in self.df_a.columns:
                    self.para_list_a.append(str(each))
                self.para_list_b = []
                for each in self.df_b.columns:
                    self.para_list_b.append(str(each))
                self.cBox_a.addItems(self.para_list_a)
                self.cBox_b.addItems(self.para_list_b)
                self.para_list = []
                self.table_res.clear()
                self.table_res.setRowCount(0)
                table_header = self.cfg["default"]["table_header"].split(',')
                self.table_res.setHorizontalHeaderLabels(table_header)
                # res = self.cmp(self.df_a,self.df_b)        
            except OSError as reason:
                logging.info(reason)
                self.statusbar.showMessage(str(reason))
        else:
            self.statusbar.showMessage("请选择正确的文件...")


    def df_trans(self,df):
        '''
        DataFrame转换:
        1,df中不存在"存货编码"和"类别"两个字段就自行增加两个空列
        2,df中类别标记为"替代料"就将"位号","数量","序号"替换为主料参数,合并单元格中除了第一行之外读出来都是空
        '''
        df = df.fillna("NULL")
        try:
            category = self.cfg["default"]["Category"]
            rep = self.cfg["default"]["Rep"]
            Designator = self.cfg["default"]["Designator"]
            Quantity = self.cfg["default"]["Quantity"]
            num = self.cfg["default"]["num"]
            index_key = self.cfg["default"]["index_key"]
            Part_number = self.cfg["default"]["Part_number"]
            Description = self.cfg["default"]["Description"]
            Manufacturer = self.cfg["default"]["Manufacturer"]
            if (category in df.columns)==False:
                logging.info("BOM中不存在字段:%s"%category)
                df[category] = self.cfg["default"]["nan"]
            if (index_key in df.columns)==False:
                logging.info("BOM中不存在字段:%s"%index_key)
                df[index_key] = self.cfg["default"]["nan"]
            if (Quantity in df.columns)==False:
                logging.info("BOM中不存在字段:%s"%Quantity)
                df[index_key] = 0
            if (Part_number in df.columns)==False:
                logging.info("BOM中不存在字段:%s"%Part_number)
                df[Part_number] = self.cfg["default"]["nan"]
                for each in df.index:
                    desc = df.loc[each,Description]
                    list1 =desc.split()
                    logging.info(desc)
                    if len(list1)>1:
                        df.loc[each,Part_number] = list1[-1]
                        df.loc[each,Manufacturer] = list1[-2]
            if True:
                for each in df.index:
                    df.loc[each,Designator] = df.loc[each,Designator].replace(" ","")

        except OSError as reason:
            logging.info("config error"+str(reason))
            category = "类别"
            rep = "替代料"
            Designator = "位号"
            Quantity = "用量"
            num = "序号"
        for each in df.index:
            ser = df.loc[each]
            if ser[category]==rep:
                index_pre = each-1
                df.loc[each,Designator] = df.loc[index_pre,Designator]
                df.loc[each,Quantity] = df.loc[index_pre,Quantity]
                df.loc[each,num] = df.loc[index_pre,num]
            if ser[Designator][-1]==',':
                df.loc[each,Designator] = df.loc[each,Designator][:-1]
        return df


    def add_para(self):
        para_a = self.cBox_a.currentText()
        para_b = self.cBox_b.currentText()
        logging.info("增加参数:File_A:%s,File_B:%s"%(para_a,para_b))
        para_info = [para_a,para_b,True]
        self.para_list.append(para_info)
        self.table_update(para_info)
        self.para_list_a.remove(para_a)
        self.para_list_b.remove(para_b)
        self.cBox_a.clear()
        self.cBox_b.clear()
        self.cBox_a.addItems(self.para_list_a)
        self.cBox_b.addItems(self.para_list_b)

    def cmp(self,):
        '''
        开始根据参数列表比对BOM
        '''
        num = self.cfg["default"]["num"]
        Designator = self.cfg["default"]["Designator"]
        Quantity = self.cfg["default"]["Quantity"]
        Description = self.cfg["default"]["Description"]
        Manufacturer = self.cfg["default"]["Manufacturer"]
        Part_number = self.cfg["default"]["Part_number"]
        split_char = self.cfg["default"]["split_char"]
        category = self.cfg["default"]["Category"]
        self.text_msg.clear()
        for each in self.df_b.index:
            ser = self.df_b.loc[each]
            if ser[num]=="NULL" and ser[Designator]=="NULL":
                index = each-1
                self.df_b.loc[each,Designator]=self.df_b.loc[index,Designator]
                self.df_b.loc[each,Quantity]=self.df_b.loc[index,Quantity]
            if ser[Designator][-1]==',':
                tmp = self.df_b.loc[each][Designator][:-1]
                self.df_b.loc[each,Designator]= tmp
                logging.info(self.df_b.loc[each,Designator])
        logging.info("df_a shape:"+str(self.df_a.shape))
        logging.info("df_b shape:"+str(self.df_b.shape))
        mode = self.cfg["default"]["mode"]
        logging.info("Command mode:"+mode)
        res_check =[]
        res_check.append(["存货编码","名称","描述","型号","位号","用量"])
        if mode=="compare":
            Da_lsit = []
            for each in self.df_a[Designator]:
                Da_lsit = Da_lsit+each.split(",")
            logging.info("BOM A 共计元器件%d个"%len(Da_lsit))
            Db_lsit = []
            for each in self.df_b[Designator]:
                Db_lsit = Db_lsit+each.split(",")
            logging.info("BOM A 共计元器件%d个"%len(Db_lsit))
            err_list_a =[]
            for each in Da_lsit:
                if (each in Db_lsit)==False:
                    err_list_a.append(each)
            logging.info("以下元器件存在于BOM A,不存在于BOM B:")
            logging.info(err_list_a)
            self.text_msg.append("以下元器件存在于BOM A,不存在于BOM B:")
            self.text_msg.append(str(err_list_a))
            err_list_b =[]
            for each in Db_lsit:
                if (each in Da_lsit)==False:
                    err_list_b.append(each)
            logging.info("以下元器件存在于BOM B,不存在于BOM A:")
            logging.info(err_list_b)
            self.text_msg.append("以下元器件存在于BOM B,不存在于BOM A:")
            self.text_msg.append(str(err_list_b))

            for id_b in self.df_b.index:
                serb = self.df_b.loc[id_b]
                logging.info(serb)
                logging.info([Designator,Quantity,Description,Manufacturer,Part_number,split_char])
                Designator_b = serb[Designator]
                Part_number_b = serb[Part_number]
                if serb[Designator]==serb[Designator]:
                    sql ="%s=='%s' "%(Designator,Designator_b)
                    logging.info("query string-->"+sql)
                    sera = self.df_a.query(sql)
                    logging.info(sera)
                    res = []
                    comment = serb[category] if serb[category]!="NULL" else ''
                    if len(sera)==0:
                        Designator_res = False
                        reason = "查找不到对应元素 "+comment
                        res = [id_b,Designator_b,reason]
                        self.update_res(res)
                    elif len(sera)>1:
                        sql ="%s=='%s' and %s=='%s'"%(Designator,Designator_b,Part_number,Part_number_b)
                        df_sel = self.df_a.query(sql)
                        logging.info("二次查找:"+sql)
                        if df_sel.shape[0]<1:
                            reason = "查找不到对应元素"
                            Designator_res = True
                            Part_number_res = False
                            res = [id_b,Designator_b,reason,Designator_res,Part_number_res]
                            self.update_res(res)
                        elif df_sel.shape[0]>1:
                            reason = "重复元素"
                            Designator_res = True
                            Part_number_res = True
                            res = [id_b,Designator_b,reason,Designator_res,Part_number_res]
                            self.update_res(res)
                        else:
                            sera = df_sel.squeeze()
                            Designator_res = True
                            reason = ''+comment
                            length = len(serb[Designator].split(split_char))
                            logging.info("计算用量:%d" %length)
                            Quantity_res = True if int(serb[Quantity])==length else False
                            Description_res = True if serb[Description]==sera[Description] else False
                            Manufacturer_res = True if serb[Manufacturer]==sera[Manufacturer] else False
                            Part_number_res = True if serb[Part_number]==sera[Part_number] else False
                            res = [id_b,Designator_b,reason,Designator_res,Part_number_res,Quantity_res,Description_res,Manufacturer_res]
                        self.update_res(res)
                    else:
                        sera = sera.squeeze()
                        Designator_res = True
                        reason = ''+comment
                        length = len(serb[Designator].split(split_char))
                        logging.info("计算用量:%d" %length)
                        Quantity_res = True if int(serb[Quantity])==length else False
                        Description_res = True if serb[Description]==sera[Description] else False
                        Manufacturer_res = True if serb[Manufacturer]==sera[Manufacturer] else False
                        Part_number_res = True if serb[Part_number]==sera[Part_number] else False
                        res = [id_b,Designator_b,reason,Designator_res,Part_number_res,Quantity_res,Description_res,Manufacturer_res]
                        self.update_res(res)
                    logging.info(Designator+"Designator_b")
                    logging.info(res)
        else:
            logging.info("Mode error...")
        return res_check

    def cell_detail(self,row,column):
        self.text_msg.clear()
        logging.info("I clicked %d行,%d列;"%(row,column))
        index = int(self.table_res.item(row,0).text())
        serb = self.df_b.loc[index]
        para_list = [self.cfg["default"]["num"],]
        para_list.append(self.cfg["default"]["Name"])
        para_list.append(self.cfg["default"]["Part_number"])
        para_list.append(self.cfg["default"]["Designator"])
        para_list.append(self.cfg["default"]["Quantity"])
        para_list.append(self.cfg["default"]["Manufacturer"])
        para_list.append(self.cfg["default"]["Description"])
        self.text_msg.append("Item in file_b:")
        message =''
        for each in para_list:
            message = message+ "%s:%s\t"%(each,serb[each])
        self.text_msg.append(message)
        df = self.df_a.query("位号 =='%s'"%serb["位号"])
        self.text_msg.append("Item in file_a:")
        if df.shape[0]==0:
            self.text_msg.append(serb["位号"]+"在BOM A中不存在")
        else:
            # BOM A 中找到符合条件的记录
            for each in df.index:
                ser = df.loc[each]
                message =''
                for each in para_list:
                    message = message+ "%s:%s\t"%(each,ser[each])
                self.text_msg.append(message)

    def table_update(self,para_info):
        index = len(self.para_list)
        self.table_para.setRowCount(index+1)
        QItem = QtWidgets.QTableWidgetItem(str(para_info[0]))
        self.table_para.setItem(index,0,QItem)
        QItem = QtWidgets.QTableWidgetItem(str(para_info[1]))
        self.table_para.setItem(index,1,QItem)
        QItem = QtWidgets.QTableWidgetItem("删除")
        self.table_para.setItem(index,2,QItem)
        if len(self.para_list_a)==1:
            self.btn_add.setEnabled(False)
        else:
            self.btn_add.setEnabled(True)

    def update_res(self,res):
        logging.info("更新比对结果...")
        row_cur = self.table_res.rowCount()
        self.table_res.setRowCount(row_cur+1)
        row = row_cur
        column = 0
        for each in res:
            QItem = QtWidgets.QTableWidgetItem(str(each))
            if each==True and type(each)==bool:
                QItem.setBackground(QBrush(QColor("green")))
                QItem.setTextAlignment(Qt.AlignCenter)
            if each==False and type(each)==bool:
                QItem.setBackground(QBrush(QColor("red")))
                QItem.setTextAlignment(Qt.AlignCenter)
            self.table_res.setItem(row,column,QItem)
            column = column+1

    def log_init(self,):    
        if not(os.path.exists("LogFile")):
            os.mkdir("LogFile")
        time_str = time.strftime('%Y_%m%d_%H%M%S', time.localtime())
        log_file_name = ".\\LogFile\\"+time_str+".txt"
        logging.basicConfig(filename=log_file_name,format="%(asctime)s %(name)s:%(levelname)s-->%(message)s",level='INFO',encoding='utf-8')


if __name__ == "__main__":
    app = QtWidgets.QApplication(sys.argv)
    window = MyWindowShow()
    window.show()
    sys.exit(app.exec_())
